﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace FlowCard
{
    public class sql
    {
        //链接字符串
        public static string MyConnString
        {
            get
            {
                //string conn = "server=192.168.1.1;database=TPAMain;uid=sa;pwd=856518armyman; ";
                string conn = "server=.;database=TPAMain;uid=sa;pwd=856518armyman; ";
                return conn;
            }
        }
        
        //根据流转卡号查询返回数据集
        public static DataTable ReturnDatasetByCardNo(string cardNo)
        {
            string Asql = "select 工单号,批号,开卡数量,型号规格, praka_table.*    from praka_table right join (select gd001 工单号,gd034 批号,gd010 开卡数量,gd007 型号规格 from gd_table where gd034 ='" + cardNo + "' ) a on a.工单号 = praka_table.aka001 ;";
            
            string connstr = MyConnString ;
            SqlConnection conn = new SqlConnection(connstr);
            if (conn.State == ConnectionState.Closed || conn.State == ConnectionState.Broken)
            {
                conn.Open();
            }
            try
            {
                SqlCommand cmd = new SqlCommand(Asql, conn);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                adapter.Fill(ds);                
                return ds.Tables[0];  
            }
            catch
            {
                return null;
            }
            finally
            {
                conn.Close();
            }
        }
        

        
        //执行单条语句不返回任何数据
        public static int ExcuteReturnInt(string sql)
        {
            string connstr = MyConnString;
            SqlConnection conn = new SqlConnection(connstr);
            if (conn.State == ConnectionState.Closed || conn.State == ConnectionState.Broken)
            {
                conn.Open();
            }
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.Text;
                int a = Convert.ToInt16(cmd.ExecuteScalar());
                return a;
            }
            catch
            {
                return 0;
            }
            finally
            {
                conn.Close();
            }
        }
        //返回标准编号及技术协议编号
        public static DataTable ReturnDataTable()
        {
            string Asql = "select * from FlowCardStandard;";
            string connstr = MyConnString;
            SqlConnection conn = new SqlConnection(connstr);
            if (conn.State == ConnectionState.Closed || conn.State == ConnectionState.Broken)
            {
                conn.Open();
            }
            try
            {
                SqlCommand cmd = new SqlCommand(Asql, conn);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                return ds.Tables[0];
            }
            catch
            {
                return null;
            }
            finally
            {
                conn.Close();
            }
        }
    }
}
